#include "DBUtils.h"
#include "GlobalConfig.h"
#include "Poco/Util/Application.h"
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/SQLite/Connector.h"
#include "Poco/StringTokenizer.h"
#include "Poco/File.h"
#include "Poco/Exception.h"
#include "..\client\log.h"
#include "dbstructure.h"
#include "objbase.h"
#include "..\client\keystate.h"

using namespace Poco::Data;
using Poco::StringTokenizer;
using Poco::Util::Application;
using Poco::Exception;

DBUtils* DBUtils::m_pSingleton = NULL;

DBUtils::DBUtils()
{
}

DBUtils::~DBUtils()
{
}

bool DBUtils::GenerateProductKey(time_t duration, int client_type)
{
    time_t gentime;
    time(&gentime);

    try
    {
        Poco::Data::SQLite::Connector::registerConnector();
        Session s(DB_TYPE,DB_NAME);
        string key;
        
        CoInitialize(NULL);
        GUID guid;
        if (S_OK == ::CoCreateGuid(&guid))
        {
            char buf[KEY_LEN+1] = {0};
            _snprintf_s(buf
                , KEY_LEN + 1
                , KEY_LEN
                , "%08X%04X%04X%02X%02X%02X%02X%02X%02X%02X%02X"
                , guid.Data1
                , guid.Data2
                , guid.Data3
                , guid.Data4[0], guid.Data4[1]
            , guid.Data4[2], guid.Data4[3], guid.Data4[4], guid.Data4[5]
            , guid.Data4[6], guid.Data4[7]);
            key = buf;
        }
        CoUninitialize();

        Statement insert(s);
        insert << "insert into keys (key,gentime,regtime,duration,state, clienttype) values (?,?,?,?,?,?)",
            use(key),use(gentime),use(gentime),use(duration),use(unuse),use(client_type);
        insert.execute();
        return true;

    }
    catch (Exception& e)
    {
    	FLogger.information("error occurred while generating key");
        FLogger.information(e.displayText());
        return false;
    }

    return false;
}

bool DBUtils::GetProductKeyInfoByKey(string product_key,db_keys* pKey)
{
    try
    {

        Poco::Data::SQLite::Connector::registerConnector();

        Session session(DB_TYPE, DB_NAME);
        Statement sel(session);

        sel<<"select * from keys where LOWER(keys.key)=LOWER(:key)",
            into(pKey->id),
            into(pKey->key),
            into(pKey->gentime),
            into(pKey->regtime),
            into(pKey->duration),
            into(pKey->state),
            into(pKey->client_type),
            use(product_key);

        if (sel.execute() != 1)
        {
            FLogger.information("no information for key(" + product_key + ")...");
            return false;
        }

        if (pKey->state != unuse)
        {
            time_t now;
            time(&now);
            if (now - pKey->regtime > pKey->duration)
            {
                pKey->state = expired;
                UpdateKeyStateWithKeyID(pKey->id, expired);
            }
        }

        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while getting product key info(" + string(e.what()) + ")...");
        return false;
    }
}

bool DBUtils::IsProductKeyExist(string product_key)
{
    if (product_key == "")
    {
        FLogger.information("invalid product_key in IsProductKeyExist...");
        return false;
    }
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();

        Session session(DB_TYPE, DB_NAME);
        Statement sel(session);

        sel<<"select id from keys where LOWER(key)=LOWER(:key)",use(product_key);

        if (sel.execute() == 1)
        {
            return true;
        }
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while checking if product key exists(" + string(e.what()) + ")...");
        return false;
    }

    return false;
}

bool DBUtils::IsProductKeyExist(int key_id)
{
    bool ret = false;

    try
    {
        Poco::Data::SQLite::Connector::registerConnector();

        Session session(DB_TYPE, DB_NAME);
        Statement sel(session);

        sel<<"select * from keys where id=:id",use(key_id);

        if (sel.execute() == 1)
        {
            ret = true;
        }
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while checking if product key exists(" + string(e.what()) + ")...");
    }

    return ret;
}

bool DBUtils::CreateInstance(DBUtils*& pProductKeyUtils)
{
    pProductKeyUtils = NULL;

    if (m_pSingleton == NULL)
    {
        m_pSingleton = new DBUtils();
    }

    pProductKeyUtils = m_pSingleton;

    return true;
}

bool DBUtils::Register(request_data* rd)
{
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();

        Session s(DB_TYPE, DB_NAME);
        // add new user
        Statement insert(s);
        insert << "INSERT INTO user (name, pass, email, hardwareid) VALUES (?,?,?,?)",
            use(rd->user),
            use(rd->pass),
            use(rd->mail),
            use(rd->hwid);

        FLogger.log("executing : " + insert.toString());
        insert.execute();

        // check if new user was added successfully.
        Statement select_user(s);
        select_user << "SELECT * from user where LOWER(name)=LOWER(:name) and LOWER(pass)=LOWER(:pass) and LOWER(email)=LOWER(:email) and LOWER(hardwareid)=LOWER(:hardwareid)"
            ,use(rd->user),use(rd->pass),use(rd->mail),use(rd->hwid);
        if (select_user.execute() != 1)
        {
            FLogger.information("insert new user failed...");
            return false;
        }

        // update key for user
        Statement update_user(s);
        update_user << "update user set key=(select id from keys where LOWER(key)=LOWER(:key)) where LOWER(name)=LOWER(:name)",use(rd->pkey),use(rd->user);
        FLogger.log("executing : " + update_user.toString());
        update_user.execute();

        // check if key was updated successfully for user
        Statement select_key(s);
        select_key << "SELECT * from user where LOWER(name)=LOWER(:name) and key = (select id from keys where LOWER(keys.key)=LOWER(:pkey))",use(rd->user),use(rd->pkey);
        if (select_key.execute() != 1)
        {
            FLogger.information("update key failed for user");
            return false;
        }

        // update key status
        Statement update_key(s);
        time_t reg_time;
        time(&reg_time);
        update_key << "update keys set state=:state, regtime=:regtime where LOWER(key) =LOWER(:key)",use(registered),use(reg_time),use(rd->pkey);
        FLogger.log("executing : " + update_key.toString());
        update_key.execute();

        // check if key state was updated successfully
        Statement select_state(s);
        int state = -1;
        select_state << "SELECT state from keys where LOWER(key)=LOWER(:key)",into(state),use(rd->pkey),now;
        if (select_state.execute() != 1 || state != registered)
        {
            FLogger.information("update state failed for user");
            return false;
        }

        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while registering.");
        FLogger.information(e.what());
        FLogger.information(e.displayText());
        return false;
    }
}


bool DBUtils::IsUserExist( string username )
{
    if (username == "")
    {
        FLogger.information("invalid username in IsUserExist...");
        return false;
    }

    try
    {
        Poco::Data::SQLite::Connector::registerConnector();

        Session s(DB_TYPE, DB_NAME);
        Statement select(s);

        select << "SELECT id FROM user where LOWER(name)=LOWER(:name)",use(username);

        if (select.execute() == 1)
        {
            return true;
        }
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while checking if user exist(" + string(e.what()) + ")...");
    	return false;
    }

    return false;
}

bool DBUtils::ChangePassword(const string user, const string new_pass)
{
    FunctionLogger al(__FUNCTION__);
    bool bRet = false;

    try
    {
	    Poco::Data::SQLite::Connector::registerConnector();
	    Session s(DB_TYPE,DB_NAME);

	    Statement insert(s);	
	    insert << "UPDATE user set pass=:new_pass where name =:name",use(new_pass),use(user);
	    insert.execute();

        Statement select(s);
        select << "SELECT * from user where pass=:pass and name=:name",use(new_pass),use(user);
        if (select.execute() != 1)
        {
            FLogger.information("change password failed");
        }

        bRet = true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while changing password");
        FLogger.information(e.what());
    }

    return bRet;
}

bool DBUtils::UpdateKeyStateWithKeyID(int key_id, int state)
{
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();
        Session s(DB_TYPE,DB_NAME);

        Statement update(s);
        update << "UPDATE keys set state=:state where id=:id",use(state),use(key_id);
        update.execute();
        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while updating key state");
        FLogger.information(e.displayText());
        return false;
    }
}

bool DBUtils::UnBind( string user )
{
    bool bRet = false;
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();
        Session s(DB_TYPE, DB_NAME);

        Statement update(s);
        update << " update user set hardwareid='' where LOWER(name)=LOWER(:name)",use(user),now;

        Statement select(s);
        int key_id;
        select << " select key from user where LOWER(name)=LOWER(:name)", into(key_id), use(user),now;

        Statement update_key_info(s);

        update_key_info << "update keys set state=:state, duration=duration-86400 where id=:id",use(unbinded),use(key_id),now;

        bRet = true;
    }
    catch (Exception& e)
    {
        FLogger.information("unbind failed");
        FLogger.information(e.displayText());
    }

    return bRet;
}

bool DBUtils::CheckExpiration( db_keys* pKey )
{
    time_t now;
    time(&now);
    if (now - pKey->regtime >= pKey->duration)
    {
        UpdateKeyStateWithKeyID(pKey->id, expired);
        return true;
    }

    return false;
}

bool DBUtils::UpdateKeyStateWithUser( string user, int state )
{
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();
        Session s(DB_TYPE, DB_NAME);

        Statement select(s);
        int key_id;
        select << " select key from user where name=:name",into(key_id),use(user),now;

        UpdateKeyStateWithKeyID(key_id, state);

        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("UpdateKeyStateWithUser failed");
        FLogger.information(e.displayText());
        return false;
    }
}

bool DBUtils::UpdateHardwareID( string user, string hardwareid )
{
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();
        Session s(DB_TYPE,DB_NAME);

        Statement update(s);
        update << "UPDATE user set hardwareid=:hwid where name=:name",use(hardwareid),use(user),now;

        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while updating hardware id");
        FLogger.information(e.displayText());
        return false;
    }
}

bool DBUtils::GetUserInfoByUsername( string username, db_user* pUser )
{
    try
    {

        Poco::Data::SQLite::Connector::registerConnector();

        Session session(DB_TYPE, DB_NAME);
        Statement sel(session);

        sel<<"select * from user where LOWER(name)=LOWER(:name)",
            into(pUser->id),
            into(pUser->name),
            into(pUser->pass),
            into(pUser->hardwareid),
            into(pUser->email),
            into(pUser->key_id),
            use(username);

        if (sel.execute() != 1)
        {
            FLogger.information("no information for key(" + username + ")...");
            return false;
        }

        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while GetUserInfo(" + string(e.what()) + ")...");
        return false;
    }
}


bool DBUtils::GetProductKeyInfoByKeyID(int key_id,db_keys* pKey)
{
    try
    {

        Poco::Data::SQLite::Connector::registerConnector();

        Session session(DB_TYPE, DB_NAME);
        Statement sel(session);

        sel<<"select * from keys where LOWER(keys.id)=LOWER(:id)",
            into(pKey->id),
            into(pKey->key),
            into(pKey->gentime),
            into(pKey->regtime),
            into(pKey->duration),
            into(pKey->state),
            into(pKey->client_type),
            use(key_id);

        if (sel.execute() != 1)
        {
            FLogger.information("no information for key");
            return false;
        }

        if (pKey->state != unuse)
        {
            time_t now;
            time(&now);
            if (now - pKey->regtime > pKey->duration)
            {
                pKey->state = expired;
                UpdateKeyStateWithKeyID(pKey->id, expired);
            }
        }

        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while GetProductKeyInfoByKeyID(" + string(e.what()) + ")...");
        return false;
    }
}

bool DBUtils::GetUserInfoByKeyID( int key_id, db_user* pUser )
{
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();

        Session s(DB_TYPE, DB_NAME);
        Statement select(s);
        string username;

        select << "select name from user where key=:key",into(username),use(key_id),now;

        if (!GetUserInfoByUsername(username, pUser))
        {
            return false;
        }

        return true;
    }
    catch (Exception& e)
    {
    	FLogger.information("error occurred while calling GetUserInfoByKeyID");
        FLogger.information(e.displayText());
        return false;
    }
}

bool DBUtils::UpdateUserKeyID( string user, int key_id )
{
    try
    {
        Poco::Data::SQLite::Connector::registerConnector();

        Session s(DB_TYPE, DB_NAME);
        Statement update(s);

        update << "update user set key=:key where name=:name",use(user),use(key_id),now;

        return true;
    }
    catch (Exception& e)
    {
        FLogger.information("error occurred while calling GetUserInfoByKeyID");
        FLogger.information(e.displayText());
        return false;
    }
}
